/*
 * @Author: zhangyang
 * @Date: 2024-06-03 11:46:45
 * @LastEditTime: 2024-06-05 16:22:12
 * @Description: 
 * @LastEditors: Please set LastEditors
 * ██████╗ ██╗     ██╗   ██╗███████╗███████╗██╗   ██╗ ██████╗ ██╗   ██╗███╗   ██╗ ██████╗               ██╗    ██╗███████╗██████╗ 
 * ██╔══██╗██║     ██║   ██║██╔════╝██╔════╝╚██╗ ██╔╝██╔═══██╗██║   ██║████╗  ██║██╔════╝               ██║    ██║██╔════╝██╔══██╗
 * ██████╔╝██║     ██║   ██║█████╗  ███████╗ ╚████╔╝ ██║   ██║██║   ██║██╔██╗ ██║██║  ███╗    █████╗    ██║ █╗ ██║█████╗  ██████╔╝
 * ██╔══██╗██║     ██║   ██║██╔══╝  ╚════██║  ╚██╔╝  ██║   ██║██║   ██║██║╚██╗██║██║   ██║    ╚════╝    ██║███╗██║██╔══╝  ██╔══██╗
 * ██████╔╝███████╗╚██████╔╝███████╗███████║   ██║   ╚██████╔╝╚██████╔╝██║ ╚████║╚██████╔╝              ╚███╔███╔╝███████╗██████╔╝
 * ╚═════╝ ╚══════╝ ╚═════╝ ╚══════╝╚══════╝   ╚═╝    ╚═════╝  ╚═════╝ ╚═╝  ╚═══╝ ╚═════╝                ╚══╝╚══╝ ╚══════╝╚═════╝
 * Copyright (c) 2024 to current by BluesYoung-web, All Rights Reserved. 
 */
import 'package:flutter/material.dart';
import '/util/log_utils.dart';
import '/database/categray.dart';
import '/database/init.dart';

class IBillItem {
  final int? id;
  final double amount;
  final int categrayId;
  final String time;
  final String? remark;
  final String? createdTime;
  final String? updatedTime;

  const IBillItem({
    this.id,
    required this.amount,
    required this.categrayId,
    required this.time,
    this.remark,
    this.createdTime,
    this.updatedTime,
  });
}

class DataBaseBill {
  static Future createBill(IBillItem billItem) async {
    var db = await DataBase.openDB();

    var now = DateTime.now();

    try {
      await db.insert(DataBase.tableBill, {
        'amount': billItem.amount,
        'categrayId': billItem.categrayId,
        'time': billItem.time,
        'remark': billItem.remark,
        'createdTime':
            billItem.createdTime ?? now.toLocal().toString().substring(0, 19),
      });
    } catch (e) {
      Log.e(e.toString());
      db.close();
    }
  }

  static Future getMonthBill(int year, int month) async {
    var now = DateTime.now();

    String startTime = '$year-${month.toString().padLeft(2, '0')}-01 00:00:00';
    String endTime = '$year-${month.toString().padLeft(2, '0')}-31 23:59:59';

    // 修正每个月的最后一天
    if (DateTime(year, month, 31).month != month) {
      if (month == 2) {
        if (DateTime(year, month, 29).month == 2) {
          endTime = '$year-02-29 23:59:59';
        } else {
          endTime = '$year-02-28 23:59:59';
        }
      } else {
        endTime = '$year-${month.toString().padLeft(2, '0')}-30 23:59:59';
      }
    }

    if (now.year == year && now.month == month) {
      endTime =
          '${now.year}-${now.month.toString().padLeft(2, '0')}-${now.day} 23:59:59';
    }

    debugPrint('startTime: $startTime');
    debugPrint('endTime: $endTime');

    var db = await DataBase.openDB();

    try {
      var res = await db.rawQuery('''
    SELECT SUM(amount) totalOutcome FROM ${DataBase.tableBill}
      WHERE time BETWEEN '$startTime' AND '$endTime'
      AND categrayId IN (SELECT autoid FROM ${DataBase.tableCategray} WHERE type = '${CategrayType.outcome.value}')
      ORDER BY time DESC
    ''');

      final outcome = res.first['totalOutcome'] as double? ?? 0.00;
      debugPrint('outcome: $outcome');

      res = await db.rawQuery('''
    SELECT SUM(amount) totalIncome FROM ${DataBase.tableBill}
      WHERE time BETWEEN '$startTime' AND '$endTime'
      AND categrayId IN (SELECT autoid FROM ${DataBase.tableCategray} WHERE type = '${CategrayType.income.value}')
      ORDER BY time DESC
    ''');

      final income = res.first['totalIncome'] as double? ?? 0.00;
      debugPrint('income: $income');

      res = await db.rawQuery('''
    SELECT *, ${DataBase.tableCategray}.type "type" FROM ${DataBase.tableBill}
      INNER JOIN ${DataBase.tableCategray} ON ${DataBase.tableBill}.categrayId = ${DataBase.tableCategray}.autoid
      WHERE time BETWEEN '$startTime' AND '$endTime'
      ORDER BY time DESC
    ''');

      debugPrint('res ${res.toString()}');

      return {
        'outcome': outcome,
        'income': income,
        // 'bills': res.map((e) => Bill.fromMap(e)).toList(),
      };
    } catch (e) {
      Log.e(e.toString());

      return {
        'outcome': 0.00,
        'income': 0.00,
        // 'bills': [],
      };
    }
  }
}
